from sqlalchemy import Column, Integer, String, ForeignKey
from model import db
from sqlalchemy.orm import relationship
from model.config import get_config
from model.user import User,UserQuestion
from model import db
from model.user import User
from sqlalchemy import func,desc
from datetime import datetime
from exception import JsonException

from cachetools import cached, TTLCache

ttl = 60
maxsize=100

@cached(TTLCache(maxsize=maxsize, ttl=ttl))
def get_user_list(page:int,limit:int):

    offset = limit * (page - 1)
    
    conf = get_config()

    new_list = []
    with db.Session() as session:

        query = session.query(User).filter(User.trueName != None).order_by(User.true_question_count.desc())

        list = query.offset(offset).limit(limit).all()

        count = query.count()
        
        for item in list:
            if not item.true_question_count:
                continue
            if item.true_question_count == "0":
                continue

            new_list.append({
                "user_id": item.id,
                "avatar": item.avatar,
                "name": item.name,
                "mobile": item.mobile,
                "trueName": item.trueName,
                "address": item.address,
                "company": item.company,
                "trueQuestionCount": item.true_question_count,
                "point": item.true_question_count * conf.point_k,
            })

    return new_list,count


@cached(TTLCache(maxsize=maxsize, ttl=ttl))
def get_area_list(page:int,limit:int):

    offset = limit * (page - 1)

    conf = get_config()

    new_list = []
    with db.Session() as session:

        query = session.query(
            User.areaInfo,
            func.sum(User.true_question_count).label('sum_true_question_count')
        ).filter(User.trueName != None).group_by(User.areaInfo).order_by(desc("sum_true_question_count"))

        list = query.offset(offset).limit(limit).all()

        count = query.count()
        
        for areaInfo,sum_true_question_count in list:
            if not sum_true_question_count:
                continue
            if sum_true_question_count=="0":
                continue

            new_list.append({
                "areaInfo": areaInfo,
                "trueQuestionCount": sum_true_question_count,
                "point": int(sum_true_question_count) * conf.point_k,
            })

    return new_list,count


@cached(TTLCache(maxsize=maxsize, ttl=ttl))
def get_company_list(page:int,limit:int):

    offset = limit * (page - 1)

    conf = get_config()

    new_list = []
    with db.Session() as session:

        query = session.query(
            User.company,
            func.sum(User.true_question_count).label('sum_true_question_count')
        ).filter(
            User.trueName != None,
            User.company != None
        ).group_by(User.company).order_by(desc("sum_true_question_count"))

        list = query.offset(offset).limit(limit).all()

        count = query.count()
        
        for company,sum_true_question_count in list:
            if not sum_true_question_count:
                continue
            if sum_true_question_count == "0":
                continue

            new_list.append({
                "company": company,
                "trueQuestionCount": sum_true_question_count,
                "point": sum_true_question_count * conf.point_k,
            })

    return new_list,count


@cached(TTLCache(maxsize=maxsize, ttl=ttl))
def get_my(user_id:int):

    conf = get_config()

    with db.Session() as session:

        info = session.query(User).filter(User.id==user_id).first()

        avatar = info.avatar

        if not info:
            raise JsonException("发生错误4", 400)
        
        my_true_question_count = info.true_question_count

        count = session.query(User).filter(User.true_question_count > my_true_question_count).count()
        
        my_ranking = count + 1

    return my_ranking, my_true_question_count * conf.point_k,avatar



@cached(TTLCache(maxsize=maxsize, ttl=ttl))
def get_test(page:int):
    return datetime.now()
